package com.proactive.webapp.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.sql.DataSource;

import com.proactive.webapp.dao.CourseCommentDAO;
import com.proactive.webapp.entity.CourseComment;
import com.proactive.webapp.entity.Member;
import com.proactive.webapp.util.DateConversion;

public class CourseCommentDAOImpl implements CourseCommentDAO{
	DateConversion dateConversion = new DateConversion();
	private DataSource dataSource;
	private Connection conn = null ;
	private ResultSet rs = null ;
	private PreparedStatement smt = null ;
	
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	@Override
	public List<CourseComment> getList(String loginAccount, int courseID, int start, int size) {
		// TODO Auto-generated method stub
		List<CourseComment> courseCommentList = new ArrayList<CourseComment>(); 
		String sql = "SELECT *,(SELECT COUNT(*) FROM course_comment_report b WHERE a.courseCommentID = b.courseCommentID) "
				+ "AS reportCount, (SELECT COUNT(*) FROM course_comment_report c WHERE a.courseCommentID = c.courseCommentID AND c.memberAccount=?) "
				+ "AS report FROM course_comment a JOIN member d ON a.memberAccount = d.memberAccount "
				+ "WHERE courseID = ? AND topCommentID IS NULL HAVING reportCount < 10 ORDER BY createDate DESC LIMIT ?,?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, loginAccount);
			smt.setInt(2, courseID);
			smt.setInt(3, start);
			smt.setInt(4, size);
			rs = smt.executeQuery();
			while(rs.next()){
				CourseComment comment = new CourseComment();
				Member member = new Member();
				member.setAccount(rs.getString("memberAccount"));
				member.setName(rs.getString("memberName"));
				member.setMemberNO(rs.getString("memberNO"));
				member.setPhotoPath(!rs.getString("memberPhoto").equals("")?rs.getString("memberPhoto"):"resources/image/default/userdefault.jpg");
				comment.setId(rs.getInt("courseCommentID"));
				comment.setContent(rs.getString("courseCommentContent"));
				comment.setAccount(rs.getString("memberAccount"));
				comment.setCreateDate(rs.getDate("createDate"));
				comment.setCreateDateString(dateConversion.ConversionYMDHM(rs.getTimestamp("createDate")));
				comment.setReportFlag(rs.getInt("report")==0?false:true);
				comment.setMember(member);
				courseCommentList.add(comment);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return courseCommentList;
	}

	@Override
	public List<CourseComment> getReplyList(String loginAccount, int commentID) {
		// TODO Auto-generated method stub
		List<CourseComment> replyCommentList = new ArrayList<CourseComment>(); 
		String sql = "SELECT *,(SELECT COUNT(*) FROM course_comment_report b WHERE a.courseCommentID = b.courseCommentID) "
				+ "AS reportCount, (SELECT COUNT(*) FROM course_comment_report c WHERE a.courseCommentID = c.courseCommentID AND c.memberAccount=?) "
				+ "AS report FROM course_comment a JOIN member d ON a.memberAccount = d.memberAccount "
				+ "WHERE topCommentID = ? HAVING reportCount < 10 ORDER BY createDate DESC";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, loginAccount);
			smt.setInt(2, commentID);
			rs = smt.executeQuery();
			while(rs.next()){
				CourseComment comment = new CourseComment();
				Member member = new Member();
				member.setAccount(rs.getString("memberAccount"));
				member.setName(rs.getString("memberName"));
				member.setMemberNO(rs.getString("memberNO"));
				member.setPhotoPath(!rs.getString("memberPhoto").equals("")?rs.getString("memberPhoto"):"resources/image/default/userdefault.jpg");
				comment.setId(rs.getInt("courseCommentID"));
				comment.setContent(rs.getString("courseCommentContent"));
				comment.setAccount(rs.getString("memberAccount"));
				comment.setCreateDate(rs.getDate("createDate"));
				comment.setCreateDateString(dateConversion.ConversionYMDHM(rs.getTimestamp("createDate")));
				comment.setReportFlag(rs.getInt("report")==0?false:true);
				comment.setMember(member);
				replyCommentList.add(comment);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return replyCommentList;
	}

	@Override
	public void insert(CourseComment comment) {
		// TODO Auto-generated method stub
		String sql = "INSERT INTO course_comment(courseID, courseCommentContent, "
				+ "memberAccount, createDate) VALUES(?,?,?,NOW())";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, comment.getCid());
			smt.setString(2, comment.getContent());
			smt.setString(3, comment.getAccount());
			smt.executeUpdate();
			
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	@Override
	public void delete(int commentID) {
		// TODO Auto-generated method stub
		String sql = "DELETE FROM course_comment WHERE courseCommentID=? OR topCommentID=?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, commentID);
			smt.setInt(2, commentID);
			smt.executeUpdate();
			
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	@Override
	public int countTotal(int courseID) {
		// TODO Auto-generated method stub
		int count = 0;
		String sql = "SELECT COUNT(*) AS countTotal,(SELECT COUNT(*) FROM course_comment_report b WHERE a.courseCommentID = b.courseCommentID) "
				+ "AS reportCount FROM course_comment a JOIN member c ON a.memberAccount = c.memberAccount "
				+ "WHERE courseID = ? AND topCommentID IS NULL GROUP BY reportCount HAVING reportCount < 10 ORDER BY createDate DESC";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, courseID);
			rs = smt.executeQuery();
			if(rs.next()){
				count = rs.getInt("countTotal");
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return count;
	}

	@Override
	public void insertReply(CourseComment comment) {
		// TODO Auto-generated method stub
		String sql = "INSERT INTO course_comment(courseID, courseCommentContent, memberAccount, topCommentID, "
				+ "createDate) VALUES(?,?,?,?,NOW())";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, comment.getCid());
			smt.setString(2, comment.getContent());
			smt.setString(3, comment.getAccount());
			smt.setInt(4, comment.getTopCommentID());
			smt.executeUpdate();
			
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	@Override
	public CourseComment get(CourseComment comment) {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM course_comment WHERE courseCommentID = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, comment.getId());
			rs = smt.executeQuery();
			if(rs.next()){
				comment.setId(rs.getInt("courseCommentID"));
				comment.setContent(rs.getString("courseCommentContent"));
				comment.setAccount(rs.getString("memberAccount"));
				comment.setCreateDate(rs.getDate("createDate"));
				comment.setCreateDateString(dateConversion.ConversionYMDHM(rs.getTimestamp("createDate")));
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return comment;
	}

}
